package com.edu.publisher.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import com.edu.publisher.bean.TestCourseUcAvgScoreDuration;
import com.edu.publisher.bean.TestPaperUcAvgScoreDuration;
import com.edu.publisher.bean.TestQuestionAnswer;
import com.edu.publisher.bean.TestUcAboutPaperLevel;

/**
 * Created on 2022/10/20.
 *
 * @author Topus
 */
public interface TestStatsMapper {
    /**
     * 获取试卷粒度的考试人数，平均分和平均时长
     * 
     * @param date
     * @param limit
     * @return
     */
    @Select("SELECT\n" + "    paper_title,\n" + "    sum_tc,\n" + "    sum_uc,\n"
        + "    CAST(sum_duration / sum_tc, 'decimal(38, 2)') AS avg_duration,\n"
        + "    CAST(sum_score / sum_tc, 'decimal(38, 2)') AS avg_score\n" + "FROM\n" + "(\n" + "    SELECT\n"
        + "        paper_title,\n" + "        sum(test_count) AS sum_tc,\n"
        + "        sum(total_duration) AS sum_duration,\n" + "        sum(total_score) AS sum_score,\n"
        + "        sum(user_count) AS sum_uc\n" + "    FROM dws_test_paper_uc_score_duration_window\n"
        + "    WHERE toYYYYMMDD(stt) = #{date}\n" + "    GROUP BY paper_title\n" + "    LIMIT #{limit}\n" + ") AS a")
    List<TestPaperUcAvgScoreDuration> selectUcAvgScoreDurationAboutPaper(@Param("date") Integer date,
        @Param("limit") Integer limit);

    /**
     * 获取课程粒度的考试人数，平均分和平均时长
     *
     * @param date
     * @param limit
     * @return
     */
    @Select("SELECT\n" + "    course_name,\n" + "    sum_tc,\n" + "    sum_uc,\n"
        + "    CAST(sum_duration / sum_tc, 'decimal(38, 2)') AS avg_duration,\n"
        + "    CAST(sum_score / sum_tc, 'decimal(38, 2)') AS avg_score\n" + "FROM\n" + "(\n" + "    SELECT\n"
        + "        course_name,\n" + "        sum(test_count) AS sum_tc,\n"
        + "        sum(total_duration) AS sum_duration,\n" + "        sum(total_score) AS sum_score,\n"
        + "        sum(user_count) AS sum_uc\n" + "    FROM dws_test_course_uc_score_duration_window\n"
        + "    WHERE toYYYYMMDD(stt) = #{date}\n" + "    GROUP BY course_name\n" + "    LIMIT #{limit}\n"
        + ") AS a order by sum_uc desc")
    List<TestCourseUcAvgScoreDuration> selectUcAvgScoreDurationAboutCourse(@Param("date") Integer date,
        @Param("limit") Integer limit);

    /**
     * 获取试卷分段粒度的考试人数
     *
     * @param date
     * @param limit
     * @param level
     * @return
     */
    @Select("SELECT\n" + "    paper_name,\n" + "    level,\n" + "    sum(user_count) AS sum_uc\n"
        + "FROM dws_test_paper_score_phase_window\n" + "WHERE (toYYYYMMDD(stt) = #{date}) AND (level = #{level})\n"
        + "GROUP BY\n" + "    paper_name,\n" + "    level\n" + "ORDER BY sum_uc DESC\n" + "LIMIT #{limit}\n")
    List<TestUcAboutPaperLevel> selectUcAboutPaperLevel(@Param("date") Integer date, @Param("limit") Integer limit,
        @Param("level") String level);

    /**
     * 获取题目粒度的答题次数成功率以及答题人数成功率
     * 
     * @param date
     * @return
     */
    @Select("SELECT\n" + "    question_name,\n" + "    sum_ac,\n" + "    sum_cac,\n"
        + "    CAST((sum_cac / sum_ac) * 100, 'decimal(38, 2)') AS correct_rate,\n" + "    sum_auc,\n"
        + "    sum_cauc,\n" + "    CAST((sum_cauc / sum_auc) * 100, 'decimal(38, 2)') AS correct_user_rate\n" + "FROM\n"
        + "(\n" + "    SELECT\n" + "        question_name,\n" + "        sum(answer_count) AS sum_ac,\n"
        + "        sum(correct_answer_count) AS sum_cac,\n" + "        sum(answer_user_count) AS sum_auc,\n"
        + "        sum(correct_answer_user_count) AS sum_cauc\n" + "    FROM dws_test_question_answer_window\n"
        + "    WHERE toYYYYMMDD(stt) = #{date}\n" + "    GROUP BY question_name\n" + ") AS a")
    List<TestQuestionAnswer> selectAcCacRate(@Param("date") Integer date);

    @Select("SELECT max(correct_rate) AS max_rate\n" + "FROM\n" + "(\n"
        + "    SELECT CAST((sum_cac / sum_ac) * 100, 'decimal(38, 2)') AS correct_rate\n" + "    FROM\n" + "    (\n"
        + "        SELECT\n" + "            question_name,\n" + "            sum(answer_count) AS sum_ac,\n"
        + "            sum(correct_answer_count) AS sum_cac\n" + "        FROM dws_test_question_answer_window\n"
        + "        WHERE toYYYYMMDD(stt) = #{date}\n" + "        GROUP BY question_name\n" + "    ) AS a\n"
        + ") AS b\n")
    Double selectMaxCorrectRate(@Param("date") Integer date);

    @Select("SELECT max(sum_ac) AS max_ac\n" + "FROM\n" + "(\n" + "    SELECT\n" + "        question_name,\n"
        + "        sum(answer_count) AS sum_ac\n" + "    FROM dws_test_question_answer_window\n"
        + "    WHERE toYYYYMMDD(stt) = #{date}\n" + "    GROUP BY question_name\n" + ") AS a")
    Long selectMaxDoQuestionCount(@Param("date") Integer date);

    @Select("SELECT max(correct_rate) AS max_rate\n" + "FROM\n" + "(\n"
        + "    SELECT CAST((sum_cauc / sum_auc) * 100, 'decimal(38, 2)') AS correct_rate\n" + "    FROM\n" + "    (\n"
        + "        SELECT\n" + "            sum(answer_user_count) AS sum_auc,\n"
        + "            sum(correct_answer_user_count) AS sum_cauc\n" + "        FROM dws_test_question_answer_window\n"
        + "        WHERE toYYYYMMDD(stt) = #{date}\n" + "        GROUP BY question_name\n" + "    ) AS a\n"
        + ") AS b\n")
    Double selectMaxCorrectUserRate(@Param("date") Integer date);
}
